/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50141
Source Host           : localhost:3306
Source Database       : sport

Target Server Type    : MYSQL
Target Server Version : 50141
File Encoding         : 65001

Date: 2014-02-27 10:13:27
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `personals_contract`
-- ----------------------------
DROP TABLE IF EXISTS `personals_contract`;
CREATE TABLE `personals_contract` (
`contractid`  int(11) NOT NULL ,
`personalid`  int(11) NOT NULL ,
`clubid`  int(11) NOT NULL ,
`fee`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' ,
`signed`  datetime NOT NULL ,
`begin`  datetime NOT NULL ,
`end`  datetime NOT NULL ,
`expiry`  datetime NOT NULL COMMENT 'Kết thúc trước thời hạn' ,
`description`  text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`ext`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '0:no/1:yes' ,
`status`  tinyint(4) NOT NULL ,
`parentid`  int(11) NOT NULL ,
`salary_in_week`  int(11) NOT NULL ,
`salary_unit`  char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`co_matchs`  smallint(6) NOT NULL DEFAULT 0 ,
`co_matchs_t`  smallint(6) NOT NULL DEFAULT 0 ,
`co_yellow_card`  smallint(6) NOT NULL DEFAULT 0 ,
`co_red_card`  smallint(6) NOT NULL DEFAULT 0 ,
`co_goals`  smallint(6) NOT NULL DEFAULT 0 ,
`co_ogs`  smallint(6) NOT NULL DEFAULT 0 ,
`created`  datetime NOT NULL ,
`modified`  datetime NULL DEFAULT NULL ,
`logid`  int(11) NOT NULL ,
PRIMARY KEY (`contractid`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of personals_contract
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Procedure structure for `personals_contract_add`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_contract_add`;

CREATE PROCEDURE `personals_contract_add`(ploginid int,ppersonalid int,pclubid int,pfee varchar(20),psigned date,pbegin date,pend date,pexpiry date,pdescription text,pext tinyint,pparentid int,psalary_in_week int,psalary_unit char(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rContractId INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(1<<1,ploginid,'personal_contract')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		INSERT personals_contract(personalid,clubid,fee,signed,`begin`,
															`end`,expiry,description,ext,status,
															parentid,psalary_in_week,salary_unit,created,
															modified,logid
		)
		VALUE(ppersonalid,pclubid,pfee,psigned,pbegin,
					pend,pexpiry,pdescription,pext,1,
					pparentid,psalary_in_week,psalary_unit,NOW(),
					NOW(),ploginid);
		SET rContractId = LAST_INSERT_ID();	
	END IF;
	SELECT rErrCode AS errCode, rContractId AS contractid;
END;

-- ----------------------------
-- Procedure structure for `personals_contract_delete`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_contract_delete`;

CREATE PROCEDURE `personals_contract_delete`(ploginid int(11),pcontractid int(11))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rType TINYINT(4) DEFAULT 0;
	DECLARE rPerm TINYINT(4) DEFAULT 0;
	SET rType = _admin_role(ploginid);
	SET rPerm = _admin_check_permission(1<<3,ploginid,'personal_contract');
	IF rErrCode=0 AND (rPerm=0 OR rType < 5) THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN		
		DELETE FROM personals_contract WHERE contractid=pcontractid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `personals_contract_get`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_contract_get`;

CREATE PROCEDURE `personals_contract_get`(pcontractid int(11))
    READS SQL DATA
BEGIN	
		SELECT A.*, B.firstname, B.lastname, C.`name`
		FROM personals_contract 
			LEFT JOIN personals B ON A.personalid=B.personalid
			LEFT JOIN clubs C ON A.clubid=C.clubid
		WHERE contractid=pcontractid;
END;

-- ----------------------------
-- Procedure structure for `personals_contract_getall`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_contract_getall`;

CREATE PROCEDURE `personals_contract_getall`(ploginid int,pstatus tinyint,poption tinyint(4)/**0:all*/,pkeyword varchar(50),pstart date,pend date,poffset int,plimit smallint(6))
    READS SQL DATA
BEGIN
	#call: 1,-1,0,'tu+khoa',null,null,0,10
	DECLARE rFoundRows INT DEFAULT 0;
	DECLARE rType TINYINT DEFAULT 0;
	DECLARE rStatus TINYINT DEFAULT 0;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_personals_contract(contractid bigint) ;	
	# permission view
	SET rType = _admin_role(ploginid);
	IF rType < 3 THEN SET rStatus = 3;
	ELSE SET rStatus = rType + 1;	
	END IF;
	SET @sqlStm = CONCAT('
												INSERT tmp_personals_contract(contractid)
												SELECT SQL_CALC_FOUND_ROWS
														A.contractid
												FROM personals_contract A ',
													IF(pkeyword != '' AND poption > 0,
														ELT(poption,
																'JOIN clubs B ON A.clubid=B.clubid',
																'JOIN personals C ON C.personalid=A.personalid'
														),''),'
												WHERE A.contractid IS NOT NULL',	
													IF(pkeyword !='' AND poption > 0,
															ELT(poption,
																	CONCAT(' AND MATCH(B.name_se,B.short,B.tag) AGAINST(',QUOTE(pkeyword),' IN BOOLEAN MODE)'),
																	CONCAT(' AND MATCH(C.fullname,C.short,C.tag) AGAINST(',QUOTE(pkeyword),' IN BOOLEAN MODE)')
															),''),
													IF(pstatus > -1, 
														CONCAT(' AND ABS(A.status)=',pstatus),
														CONCAT(' AND ABS(A.status)<',rStatus)
													),													
													IF(pstart <> '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(A.created,10) BETWEEN ''',pstart,''' AND ''',pend,''''),''),
													IF(pstart <> '0000-00-00' AND pend = '0000-00-00', CONCAT(' AND LEFT(A.created,10)=''',pstart,''''),''),
													IF(pstart = '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(A.created,10)=''',pend,''''),''),
												' ORDER BY A.contractid DESC
												LIMIT ',poffset,',',plimit
											);

	PREPARE stmt FROM @sqlStm;
	EXECUTE stmt;

	SET rFoundRows = FOUND_ROWS();
	
	SELECT SQL_SMALL_RESULT rFoundRows AS foundRows, B.*
	FROM tmp_personals_contract A JOIN personals_contract B ON A.contractid=B.contractid;

	DROP TEMPORARY TABLE IF EXISTS tmp_personals_contract;	
END;

-- ----------------------------
-- Procedure structure for `personals_contract_update`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_contract_update`;

CREATE PROCEDURE `personals_contract_update`(ploginid int,pcontractid int,ppersonalid int,pclubid int,pfee varchar(20),psigned date,pbegin date,pend date,pexpiry date,pdescription text,pext tinyint,pparentid int,psalary_in_week int,psalary_unit char(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(1<<2,ploginid,'personal_contract')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		UPDATE personals_contract
		SET fee=pfee,
				signed=psigned,
				`begin`=pbegin,
				`end`=pend,
				`expiry`=pexpiry,
				`description`=pdescription,
				`ext`=pext,
				`parentid`=pparentid,
				`salary_in_week`=ppsalary_in_week,
				`salary_unit`=psalary_unit,
				`modified`=NOW(),
				`logid`=ploginid
		WHERE personalid=ppersonalid AND clubid=pclubid AND contractid=pcontractid;
	END IF;
	SELECT rErrCode AS errCode, rContractId AS contractid;
END;

-- ----------------------------
-- Procedure structure for `personals_contract_update_status`
-- ----------------------------
DROP PROCEDURE IF EXISTS `personals_contract_update_status`;

CREATE PROCEDURE `personals_contract_update_status`(ploginid int(11),pcontractid int(11),pstatus tinyint(4),pperm tinyint(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(pperm,ploginid,'personal_contract')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode=0 THEN		
		UPDATE personals_contract SET `status`=pstatus WHERE contractid=pcontractid;	
	END IF;
	SELECT rErrCode AS errCode;
END;
